// databaseOperation.js

var dbName = "cfentDB";
var dbPath = "fs://res/cfentDB.sqlite";

//数据库方法准备好(在用到数据库函数的地方替换掉apiready方法即可，且不可与apiready同时使用，不然会报错)
var DBReady;

/*一级封装*/
//打开数据库
var openDB;
//关闭数据库
var closeDB;
//执行SQL语句
var executeSQL;
//查询SQL语句
var selectSQL;
//删除所有数据createTables
var deleteCache;
//创建所有表
var createTables;
//删除所有表
var dropTables;

/*二级封装*/
//插入数据	传入：表明、对象(比如：{key : 'value', key: 'value'})插入数据
var insertData;
//查询数据	传入：表名、page、rows取出所有数据
var selectData;

apiready = function(){
    var db = api.require('db');

    openDB = function(callback) {
		db.openDatabase({
			name: dbName,
			path: dbPath
		}, function(ret, err) {
			callback(ret, err);
		});
	}
	closeDB = function(callback) {

		db.closeDatabase({
			name: dbName
		}, function(ret, err) {
			callback(ret, err);
		});
	}

	executeSQL = function(sql, callback) {
		db.executeSql({
			name: dbName,
			sql: sql
		}, function(ret, err) {
			callback(ret, err);
		});
	}
	selectSQL = function(sql, callback) {

		db.selectSql({
			name: dbName,
			sql: sql
		}, function(ret, err) {
			callback(ret, err);
		});
	}
	var creattTableSqls = [
		'CREATE TABLE IF NOT EXISTS t_sign_record (id integer PRIMARY KEY NOT NULL,uid integer NOT NULL,jid integer NOT NULL,lat integer NOT NULL,lon integer NOT NULL,address text NOT NULL,signtime text,mood integer)',
		'CREATE TABLE IF NOT EXISTS t_message (id integer PRIMARY KEY NOT NULL,msgid integer NOT NULL,uid integer NOT NULL,jid integer NOT NULL,title text,content text,richtext text,basetype integer,subtype integer,readstatus integer DEFAULT 0,receiptime text,uniquekey text NOT NULL UNIQUE)',
		'CREATE TABLE IF NOT EXISTS exam (eid integer PRIMARY KEY NOT NULL,ename text,enumber text,efen text,etype text,dtime text,e_big_exam text,e_display_cj text,ctime text,stime text,etime text,jtid text,sjid text,uid integer,eshunxu text);',
		'CREATE TABLE IF NOT EXISTS exam_chengji (ecid integer PRIMARY KEY NOT NULL,euserid integer,examid integer,etime text,chengji text,cjjieguo text,signature text,is_jiaoyu text,is_pingjuan text,types text,sid integer,jid integer,efen text,zfen text,ename text);',
		'CREATE TABLE IF NOT EXISTS t_study_plan (id integer PRIMARY KEY NOT NULL,title text,jid text,note text,btime text,etime text,status text,number text,img text,selected text,uid integer,planid integer NOT NULL,uniquekey text UNIQUE);',
		'CREATE TABLE IF NOT EXISTS t_study_chapter (id integer PRIMARY KEY NOT NULL,cid text,handout text,is_encr text,baidu_cloud_id text,baidu_cloud_id_url text,rid text,cdetailtitle text,ctime text,etime text,other text,exam_id text,pid text,path text,content_type text,bewrite text,is_dis_bewrite text,hdresourceType text,weights text,isclose text,mtime text,paragh text,view_path text,docpath text,localpath text,sourceUrl text,chapter_id integer NOT NULL,UNIQUE (chapter_id DESC));',
		'CREATE TABLE IF NOT EXISTS t_study_comment (id integer PRIMARY KEY NOT NULL,comment text,uid text,ctime text,score text,cid text,commentid integer NOT NULL,uimage text,uname text,to_uname text,UNIQUE (commentid DESC));',
		'CREATE TABLE IF NOT EXISTS t_study_comment_reply (id integer PRIMARY KEY NOT NULL,commentid integer,replyid integer NOT NULL,replyuid integer,replycontent text,replyuname text,replydate text,replyuimage text,UNIQUE (replyid DESC));',
		'CREATE TABLE IF NOT EXISTS t_study_course (id integer PRIMARY KEY NOT NULL,is_score text,courseid text NOT NULL,examid text,crid text,source text,isCollect text,jid text,planid text,coursename text,coverimages text,socre text,viewcount text,commentcount text,cjd text,ctype text default 1,cstatus text,selected text,coursetype text,part text,uid integer,haveexam integer default 0,passmark integer,examscore integer,title text,speaker text,uniquekey text UNIQUE);',
		'CREATE TABLE IF NOT EXISTS t_study_course_data (id integer PRIMARY KEY NOT NULL,cid text,data_id text NOT NULL,names text,real_url text,size text,ctime text,vid text,vcid text,material text,sort text,is_downloaded text default 0,localpath text,UNIQUE (data_id DESC));',
		'CREATE TABLE IF NOT EXISTS t_study_course_category (id integer PRIMARY KEY NOT NULL,jid integer NOT NULL,lessoncategory text NOT NULL);',
		'CREATE TABLE IF NOT EXISTS t_study_course_info (id integer PRIMARY KEY NOT NULL,coursename text,courseid text NOT NULL,courseinfo text,coverimages text,speaker text,alltime text,examid text default 0,checks integer,udapp text default 0,complete text,iscache text default 0,cachepath text,coursesize text default 0,UNIQUE (courseid DESC));',
		'CREATE TABLE IF NOT EXISTS t_study_exam (id integer PRIMARY KEY NOT NULL,examid text,sid text,examstatus integer default 0,courseid text NOT NULL,uid text,pid text,uniquekey text UNIQUE);',
		'CREATE TABLE IF NOT EXISTS t_study_note (id integer PRIMARY KEY NOT NULL,uid integer,cid integer,jvid integer,ctime text,comment text,learn_task_id integer,version text,note_id integer NOT NULL,UNIQUE (note_id DESC));',
		'CREATE TABLE IF NOT EXISTS t_study_course_record (id integer PRIMARY KEY NOT NULL,uid text,jid text,pid text,cid text,crid text,vid text,types text,timenum text default 0,origtime text,status text default 0,playtime text default 0,submitstatus text default 0,uniquekey text UNIQUE);',
		'CREATE TABLE IF NOT EXISTS t_study_course_record_detail (id integer PRIMARY KEY NOT NULL,uid text,jid text,pid text,cid text,vid text,instime text,submitstatus text default 0);',
		'CREATE TABLE IF NOT EXISTS test_item (id integer PRIMARY KEY NOT NULL,examid text,sid text,vid text,cid text,pid text,testid text,dtime text,efen text,fullscore text,qid text,qtype text,questions text,qfenshu text,qdaan text,udaan text,answer text,qxuanxiang text,jid integer,isfalse text,iscollectioned text,stime text,uid integer, signature text, uniquekey text UNIQUE);',
		'CREATE TABLE IF NOT EXISTS t_study_test_record (id integer PRIMARY KEY NOT NULL,uid text,pid text,chapterid text,testid text,isanswer text default 0,uniquekey text UNIQUE);',
		'CREATE TABLE IF NOT EXISTS t_attachment (id integer PRIMARY KEY NOT NULL,url text,name text,size text,localpath text,isdownload text default 0);'
		// ,'CREATE TABLE IF NOT EXISTS t_study_practice (id integer PRIMARY KEY NOT)'
	];

	createTables = function(callback) {
		openDB(function(ret, err) {
			if (ret.status) {
				var j = 0;
				var k = 0;
				var tableNum = creattTableSqls.length;
				for (var i = 0; i < tableNum; i++) {
					var sql = creattTableSqls[i];
					executeSQL(sql, function(ret, err) {
						if (ret.status) {
							k++;
						}
						j++;
						if (j == tableNum) {
							callback(k == j);
						}
					});
				}
			}
		});
	}
	var dropTableSqls = [
		'DROP TABLE IF EXISTS t_sign_record;',
		'DROP TABLE IF EXISTS t_message;',
		'DROP TABLE IF EXISTS exam;',
		'DROP TABLE IF EXISTS exam_chengji;',
		'DROP TABLE IF EXISTS t_study_plan;',
		'DROP TABLE IF EXISTS t_study_chapter;',
		'DROP TABLE IF EXISTS t_study_comment;',
		'DROP TABLE IF EXISTS t_study_comment_reply;',
		'DROP TABLE IF EXISTS t_study_course;',
		'DROP TABLE IF EXISTS t_study_course_data;',
		'DROP TABLE IF EXISTS t_study_course_category;',
		'DROP TABLE IF EXISTS t_study_course_info;',
		'DROP TABLE IF EXISTS t_study_exam;',
		'DROP TABLE IF EXISTS t_study_note;',
		'DROP TABLE IF EXISTS t_study_course_record;',
		'DROP TABLE IF EXISTS t_study_course_record_detail;',
		'DROP TABLE IF EXISTS test_item;',
		'DROP TABLE IF EXISTS t_study_test_record;',
		'DROP TABLE IF EXISTS t_attachment;'
	];
	dropTables = function(callback) {
		openDB(function(ret, err) {
			if (ret.status) {
				var j = 0;
				for (var i = 0; i < dropTableSqls.length; i++) {
					var sql = dropTableSqls[i];
					executeSQL(sql, function(ret, err) {
						if (ret.status) {
							j++;
							if (j == dropTableSqls.length) {
								callback();
							}
						}
					});
				}
			}
		});
	}
	/*
	所有表名
	t_sign_record
	*/
	var tables = [
		't_sign_record',
		't_message',
		'exam',
		'exam_chengji',
		't_study_plan',
		't_study_chapter',
		't_study_comment',
		't_study_comment_reply',
		't_study_course',
		't_study_course_data',
		't_study_course_category',
		't_study_course_info',
		't_study_exam',
		't_study_note',
		't_study_course_record',
		't_study_course_record_detail',
		'test_item',
		't_study_test_record',
		't_attachment'
	];
	deleteCache = function(callback) {

		var j = 0;
		for (var i = 0; i < tables.length; i++) {
			var tableName = tables[i];
			var sql = "DELETE FROM " + tableName + ";";
			executeSQL(sql, function(ret, err) {
				j++;
				if (j == tables.length) {
					callback();
				}
			});
		}
	}
	/*
	 *	tableName: 表名 String
	 *	params:    参数	object(要插入数据的key和value)
	 */
	insertData = function(tableName, params, callback) {

		var keys = '';
		var values = '';

		for (var key in params) {
			keys += key + ',';
			values += '\'' + params[key] + '\',';
		}
		//去掉最后一个‘,’
		keys = keys.substring(0, keys.length - 1);
		values = values.substring(0, values.length - 1);

		var sql = "INSERT INTO " + tableName + "(" + keys + ")" + " VALUES" + "(" + values + ");";
		executeSQL(sql, callback);
	}
	selectData = function(tableName, page, rows, callback) {

		//开始查询位置
		var limitStart = 0;
		//查询条数
		var limitCount = 10;
		if (page > 0) {
			limitStart = (page - 1) * rows;
		}
		if (rows > 0) {
			limitCount = rows;
		}

		var sql = "SELECT * FROM " + tableName + " LIMIT " + limitStart + ", " + limitCount + ";";
		selectSQL(sql, callback);
	}

	DBReady();

}
